﻿using System;
using System.Collections.Generic;
using System.ComponentModel;

using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;

namespace BenhVien
{
    public partial class FormHSBNKNgoaiTru : Form
    {
        SqlConnection con;
        private System.Data.DataTable dtBNNGT = new DataTable("tblBNBNNGT");
        private SqlDataAdapter daBNNGT = new SqlDataAdapter();

        private void Connect()
        {
            try
            {
                con = new SqlConnection("Data Source=192.168.1.200,1433;Server=IWANTWIN-PC;user id=sa;password=123456;database=QLBenhVien; Connection Timeout=15");
                con.Open();
            }
            catch
            {
                MessageBox.Show("Ket noi co so du lieu that bai!", "Loi", MessageBoxButtons.OK);
            }

        }
        private void getData()
        {
            SqlCommand command = new SqlCommand();
            command.Connection = con;
            command.CommandType = CommandType.Text;
            command.CommandText = @"Select 
                                        MaBN as N'MaBN',
                                        Ho as N'Ho',
                                        Ten as N'Ten',
                                        DiaChi as N'Dia Chi',
                                        NgaySinh as N'Ngay Sinh',
                                        NgayDangKy as N'Ngay Dang Ky',
                                        (Case GioiTinh When '1' Then N'Nam'
                                                       When '0' Then N'Nu'
                                                        else 'khong xac dinh' end)  as N'Gioi Tinh',
                                        SoDienThoai as N'So Dien Thoai',
                                        HonNhan as N'Hon Nhan',
                                        TenNguoiThan as N'Ten Nguoi Than',
                                        SoDienThoaiNguoiThan as N'SDT Nguoi Than',
                                        TenBacSiDiaPhuong as N'Ten BSDP',
                                        DiaChiBSDP as N'Dia Chi BSDP',
                                        SoDienThoaiBSDP as N'SDT Bac Si DP',
                                        MaPhongNgoaiTru as N'Ma Phong Ngoai Tru'

                                    From BenhNhan";
            daBNNGT.SelectCommand = command;
            daBNNGT.Fill(dtBNNGT);
            //Chu y cai nay

            if (dtBNNGT.Rows.Count > 0)
            {
                grvBNNGT.DataSource = dtBNNGT;
            }
            else
            {
                MessageBox.Show("Khong tim thay du lieu dac biet", "Loi", MessageBoxButtons.OK);
            }
        }
        private void binding()
        {
            textMaBN.DataBindings.Clear();
            textMaBN.DataBindings.Add("Text", grvBNNGT.DataSource, "MaBN");
            textHoBN.DataBindings.Clear();
            textHoBN.DataBindings.Add("Text", grvBNNGT.DataSource, "Ho");
            textTenBN.DataBindings.Clear();
            textTenBN.DataBindings.Add("Text", grvBNNGT.DataSource, "Ten");
            textDiaChiBN.DataBindings.Clear();
            textDiaChiBN.DataBindings.Add("Text", grvBNNGT.DataSource, "Dia Chi");
            textNgaySinhBN.DataBindings.Clear();
            textNgaySinhBN.DataBindings.Add("Text", grvBNNGT.DataSource, "Ngay Sinh");
            textNgayDangKy.DataBindings.Clear();
            textNgayDangKy.DataBindings.Add("Text", grvBNNGT.DataSource, "Ngay Dang Ky");
            textGioiTinh.DataBindings.Clear();
            textGioiTinh.DataBindings.Add("Text", grvBNNGT.DataSource, "Gioi Tinh");
            textSoDienThoaiBN.DataBindings.Clear();
            textSoDienThoaiBN.DataBindings.Add("Text", grvBNNGT.DataSource, "So Dien Thoai");
            textHonNhan.DataBindings.Clear();
            textHonNhan.DataBindings.Add("Text", grvBNNGT.DataSource, "Hon Nhan");
            textTenNguoiThan.DataBindings.Clear();
            textTenNguoiThan.DataBindings.Add("Text", grvBNNGT.DataSource, "Ten Nguoi Than");
            textSDTNguoiThan.DataBindings.Clear();
            textSDTNguoiThan.DataBindings.Add("Text", grvBNNGT.DataSource, "SDT Nguoi Than");
            textTenBSDP.DataBindings.Clear();
            textTenBSDP.DataBindings.Add("Text", grvBNNGT.DataSource, "Ten BSDP");
            textDiaChiBSDP.DataBindings.Clear();
            textDiaChiBSDP.DataBindings.Add("Text", grvBNNGT.DataSource, "Dia Chi BSDP");
            textSoDienThoaiBSDP.DataBindings.Clear();
            textSoDienThoaiBSDP.DataBindings.Add("Text", grvBNNGT.DataSource, "SDT Bac Si DP");
            textMaPhongNgoaiTru.DataBindings.Clear();
            textMaPhongNgoaiTru.DataBindings.Add("Text", grvBNNGT.DataSource, "Ma Phong Ngoai Tru");

        }

        private void disconnect()
        {
            con.Close();

        }




        public FormHSBNKNgoaiTru()
        {
            InitializeComponent();
        }

        private void FormHSBNKNgoaiTru_Load(object sender, EventArgs e)
        {
            Connect();
            getData();
            binding();
            disconnect();
        }

        private void btInThongTin_Click(object sender, EventArgs e)
        {
            Excel.Application app = new Excel.Application();
            Excel.Workbook wb = app.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
            Excel._Worksheet ws = (Excel._Worksheet)app.ActiveSheet;

            ws.Name = "Thong tin chi tiet";
            app.Visible = true;

            ws.Cells[1, 1] = "MaBN";
            ws.Cells[1, 2] = "Ho BN";
            ws.Cells[1, 3] = "Ten BN";
            ws.Cells[1, 4] = "Dia Chi";
            ws.Cells[1, 5] = "Ngay Sinh";

            ws.Cells[1, 6] = "Ngay Dang Ky";
            ws.Cells[1, 7] = "Gioi Tinh ";
            ws.Cells[1, 8] = "So Dien Thoai";
            ws.Cells[1, 9] = "Hon Nhan";
            ws.Cells[1, 10] = "Ten Nguoi Than";

            ws.Cells[1, 11] = "SDT Nguoi Than";
            ws.Cells[1, 12] = "Ten Bac si DP";
            ws.Cells[1, 13] = "Dia Chi BSDP";
            ws.Cells[1, 14] = "SDT BSDP";
            ws.Cells[1, 15] = "Ma Phong Ngoai Tru";

            ws.Cells[2, 1] = textMaBN.Text;
            ws.Cells[2, 2] = textHoBN.Text;
            ws.Cells[2, 3] = textTenBN.Text;
            ws.Cells[2, 4] = textDiaChiBN.Text;
            ws.Cells[2, 5] = textNgaySinhBN.Text;

            ws.Cells[2, 6] = textNgayDangKy.Text;
            ws.Cells[2, 7] = textGioiTinh.Text;
            ws.Cells[2, 8] = textSoDienThoaiBN.Text;
            ws.Cells[2, 9] = textHonNhan.Text;
            ws.Cells[2, 10] = textTenNguoiThan.Text;

            ws.Cells[2, 11] = textSDTNguoiThan.Text;
            ws.Cells[2, 12] = textTenBSDP.Text;
            ws.Cells[2, 13] = textDiaChiBSDP.Text;
            ws.Cells[2, 14] = textSoDienThoaiBSDP.Text;
            ws.Cells[2, 15] = textMaPhongNgoaiTru.Text;
        }

        private void btThoat_Click(object sender, System.EventArgs e)
        {
            this.Close();
            //FormQLBenhVien form = new FormQLBenhVien();
            //form.Show();
        }

        private void btTimKiem_Click(object sender, EventArgs e)
        {
            dtBNNGT.Clear();
            SqlCommand command = new SqlCommand();
            command.Connection = con;
            command.CommandType = CommandType.Text;
            command.CommandText = @"Select  
                                        MaBN as N'MaBN',
                                        Ho as N'Ho',
                                        Ten as N'Ten',
                                        DiaChi as N'Dia Chi',
                                        NgaySinh as N'Ngay Sinh',
                                        NgayDangKy as N'Ngay Dang Ky',
                                        (Case GioiTinh When '1' Then N'Nam'
                                                       When '0' Then N'Nu'
                                                        else 'khong xac dinh' end)  as N'Gioi Tinh',
                                        SoDienThoai as N'So Dien Thoai',
                                        HonNhan as N'Hon Nhan',
                                        TenNguoiThan as N'Ten Nguoi Than',
                                        SoDienThoaiNguoiThan as N'SDT Nguoi Than',
                                        TenBacSiDiaPhuong as N'Ten BSDP',
                                        DiaChiBSDP as N'Dia Chi BSDP',
                                        SoDienThoaiBSDP as N'SDT Bac Si DP',
                                        MaPhongNgoaiTru as N'Ma Phong Ngoai Tru'
                                    From BenhNhan
                                    Where Ho LIKE '%'+@HoTK+'%' AND Ten LIKE '%'+@TenTK+'%' ";

            command.Parameters.Add("@HoTK", SqlDbType.NVarChar, 50).Value = textTKHoBN.Text;
            command.Parameters.Add("@TenTK", SqlDbType.NVarChar, 50).Value = textTKTenBN.Text;
            daBNNGT.SelectCommand = command;
            daBNNGT.Fill(dtBNNGT);
            if (dtBNNGT.Rows.Count > 0)
            {

                grvBNNGT.DataSource = dtBNNGT;
            }
            else
            {
                MessageBox.Show("Khong co du lieu", "Loi", MessageBoxButtons.OK);
            }
        }

        private void btXoa_Click(object sender, EventArgs e)
        {
            textTKHoBN.Clear();
            textTKTenBN.Clear();
        }

        

       

       

    }
}
